package com.jy.dao.impl;

import java.sql.*;
import java.util.List;

import com.jy.dao.UserDao;
import com.jy.entity.*;
import com.jy.util.DbUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class UserDaoImpl implements UserDao {
	QueryRunner runner=new QueryRunner();

	//查询学生
	@Override
	public List<Student> getstu(String stname,String stpass) throws SQLException {
		Connection conn= DbUtil.getconn();
		String sql="select *from student where stname=? and stpass=?";
		List<Student> list=runner.query(conn, sql,new BeanListHandler<Student>(Student.class),stname,stpass);
		conn.close();
		return list;
	}

	//添加学生
	@Override
	public int addstu(Student stu) throws SQLException {
		String sql="insert into student(classId,stname,stpass,stphone,stsex,stwx,stavatar) values(?,?,?,?,?,?,?)";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[7];
		p[0]=stu.getClassid();
		p[1]=stu.getStname();
		p[2]=stu.getStpass();
		p[3]=stu.getStphone();
		p[4]=stu.getStsex();
		p[5]=stu.getStwx();
		p[6]=stu.getStavatar();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}

	@Override
	public List<Classes> getclasses(String sql, Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		List<Classes> list=runner.query(conn,sql,new BeanListHandler<Classes>(Classes.class),objects);
		conn.close();
		return list;
	}

	//学生登录
	@Override
	public Student login(String stname, String stpass) throws Exception {
		Connection conn= DbUtil.getconn();
		String sql="select *from student where stname=? and stpass=?";
		Student list=runner.query(conn,sql,new BeanHandler<Student>(Student.class),stname,stpass);
		conn.close();
		return list;
	}

	//老师登录
	@Override
	public Teacher login2(String tname, String tpass) throws Exception {
		Connection conn=DbUtil.getconn();
		String sql="select *from teacher where tname=? and tpass=?";
		Teacher list=runner.query(conn,sql,new BeanHandler<Teacher>(Teacher.class),tname,tpass);
		conn.close();
		return list;
	}



	//学生找回密码
	@Override
	public Student pwd(String stname, String stphone) throws Exception {
		Connection conn= DbUtil.getconn();
		String sql="select *from student where stname=? and stphone=?";
		Student list=runner.query(conn,sql,new BeanHandler<Student>(Student.class),stname,stphone);
		conn.close();
		return list;
	}

	//学生修改密码
	@Override
	public int stupdate(Student obj) throws Exception {
		Connection conn= DbUtil.getconn();
		String sql="update student set stpass=? where stname=?";
		Object p[]=new Object[2];
		p[0]=obj.getStpass();
		p[1]=obj.getStname();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;

	}

	//修改学生信息
	@Override
	public int updatestuinfo(Student obj) throws Exception {
		Connection conn= DbUtil.getconn();
		String sql="update student set stpass=?,stphone=?,stsex=?,stwx=?,stavatar=? where stname=?";
		Object p[]=new Object[6];
		p[0]=obj.getStpass();
		p[5]=obj.getStname();
		p[1]=obj.getStphone();
		p[2]=obj.getStsex();
		p[3]=obj.getStwx();
		p[4]=obj.getStavatar();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}

	//修改老师信息
	@Override
	public int updateteainfo(Teacher obj) throws Exception {
		Connection conn= DbUtil.getconn();
		String sql="update teacher set tpass=?,tphone=?,thavatar=? where tname=?";
		Object p[]=new Object[4];
		p[0]=obj.getTpass();
		p[1]=obj.getTphone();
		p[2]=obj.getThavatar();
		p[3]=obj.getTname();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}
	//老师找回密码
	@Override
	public Teacher pwd2(String tname, String tphonte) throws Exception {
		Connection conn=DbUtil.getconn();
		String sql="select *from teacher where tname=? and tphone=?";
		Teacher list=runner.query(conn,sql,new BeanHandler<Teacher>(Teacher.class),tname,tphonte);
		return list;
	}

	//老师修改密码
	@Override
	public int tcupdate(Teacher obj) throws Exception {
		Connection conn= DbUtil.getconn();
		String sql="update teacher set tpass=? where tname=?";
		Object p[]=new Object[2];
		p[0]=obj.getTpass();
		p[1]=obj.getTname();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}

	//注册码
	@Override
	public register getzcm() throws Exception {
		Connection conn= DbUtil.getconn();
		String sql="select *from register";
		register reg=runner.query(conn,sql,new BeanHandler<register>(register.class));
		conn.close();
		return reg;
	}

	//查询班级学生
	@Override
	public List<Student> findclass(int classId) throws SQLException {
			Connection conn= DbUtil.getconn();
			String sql="select *from student where classId=?";
			List<Student> list=runner.query(conn, sql,new BeanListHandler<Student>(Student.class),classId);
			conn.close();
			return list;
	}

	//查询班级详情
	@Override
	public Classes classmsg(int classId) throws SQLException {
		Connection conn= DbUtil.getconn();
		String sql="select *from classes where classId=?";
		Classes c=runner.query(conn, sql,new BeanHandler<Classes>(Classes.class),classId);
		conn.close();
		return c;
	}

	//查询班级详情2
	@Override
	public Classes classmsg1(String sql,Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		Classes c=runner.query(conn, sql,new BeanHandler<Classes>(Classes.class),objects);
		conn.close();
		return c;
	}

	@Override
	public int addtea(Teacher tea) throws SQLException {
		String sql="insert into teacher(tname,tpass,tphone,tjob) values(?,?,?,?)";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[4];
		p[0]=tea.getTname();
		p[1]=tea.getTpass();
		p[2]=tea.getTphone();
		p[3]=tea.getTjob();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}

	@Override
	public List<Student> getyh(String stname) throws SQLException {
		Connection conn= DbUtil.getconn();
		String sql="select *from student where stname=?";
		List<Student> list=runner.query(conn, sql,new BeanListHandler<Student>(Student.class),stname);
		conn.close();
		return list;
	}

	@Override
	public List<Teacher> getls(String tname) throws SQLException {
		Connection conn= DbUtil.getconn();
		String sql="select *from teacher where tname=?";
		List<Teacher> list=runner.query(conn, sql,new BeanListHandler<Teacher>(Teacher.class),tname);
		conn.close();
		return list;
	}

	@Override
	public Teacher getteacher(String sql, Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		Teacher t=runner.query(conn, sql,new BeanHandler<>(Teacher.class),objects);
		conn.close();
		return t;
	}

	//查询题目分类
	@Override
	public List<book> getbook(String sql, Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		List<book> list=runner.query(conn, sql,new BeanListHandler<book>(book.class),objects);
		conn.close();
		return list;
	}


	//查询题目分类放入实体类
	@Override
	public book getbook2(String sql, Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		book list=runner.query(conn, sql,new BeanHandler<book>(book.class),objects);
		conn.close();
		return list;
	}
	@Override
	public List<Student> getclsstu(int classid) throws SQLException {
		Connection conn= DbUtil.getconn();
		String sql="select *from student where classId=?";
		List<Student> list=runner.query(conn, sql,new BeanListHandler<Student>(Student.class),classid);
		conn.close();
		return list;
	}

	@Override
	public Student getstuid(String stname) throws SQLException {
		Connection conn= DbUtil.getconn();
		String sql="select *from student where stname=?";
		Student s=runner.query(conn, sql,new BeanHandler<>(Student.class),stname);
		conn.close();
		return s;
	}

	//查询考试
	@Override
	public List<Timetest> gettimetest(String sql, Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		List<Timetest> list=runner.query(conn, sql,new BeanListHandler<Timetest>(Timetest.class),objects);
		conn.close();
		return list;
	}

	//添加考试
	@Override
	public int addtest(Timetest test) throws SQLException {
		String sql="insert into exam(exam_name,exam_avatar,exam_desc,exam_time_limit,exam_start_date,exam_end_date,exam_creator_id,create_time,exam_type,exam_zt,exam_classid) values(?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[11];
		p[0]=test.getExam_name();
		p[1]=test.getExam_avatar();
		p[2]=test.getExam_desc();
		p[3]=test.getExam_time_limit();
		p[4]=test.getExam_start_date();
		p[5]=test.getExam_end_date();
		p[6]=test.getExam_creator_id();
		p[7]=test.getCreate_time();
		p[8]=test.getExam_type();
		p[9]=test.getExam_zt();
		p[10]=test.getExam_classid();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}
	//修改考试
	@Override
	public int updatetest(Timetest t) throws SQLException {
		String sql="update exam set exam_question_ids=?,exam_score=?,exam_zt=? where exam_id=?";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[4];
		p[0]=t.getExam_question_ids();
		p[1]=t.getExam_score();
		p[2]=t.getExam_zt();
		p[3]=t.getExam_id();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}
	//实体类接收查询试卷
	@Override
	public Timetest getTimetest(String sql, Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		Timetest time=runner.query(conn, sql,new BeanHandler<Timetest>(Timetest.class),objects);
		conn.close();
		return time;
	}
	@Override
	public List<Question> gettestquestion(String sql, Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		List<Question> list=runner.query(conn, sql,new BeanListHandler<Question>(Question.class),objects);
		conn.close();
		return list;
	}
	//查询题目
	@Override
	public Question getquestion(String sql, Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		Question q=runner.query(conn, sql,new BeanHandler<Question>(Question.class),objects);
		conn.close();
		return q;
	}
	//查询视频
	@Override
	public List<Video> getvideo(String sql, Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		List<Video> list=runner.query(conn, sql,new BeanListHandler<Video>(Video.class),objects);
		conn.close();
		return list;
	}

	//添加视频
	@Override
	public int addvideo(Video t) throws SQLException {
		String sql="insert into video(vname,vdesc,vtype,vurl,classid,teacherid,vtime) values(?,?,?,?,?,?,?)";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[7];
		p[0]=t.getVname();
		p[1]=t.getVdesc();
		p[2]=t.getVtype();
		p[3]=t.getVurl();
		p[4]=t.getClassid();
		p[5]=t.getTeacherid();
		p[6]=t.getVtime();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}

	/**
	 * 通过考试ID查询对应的题目消息
	 * @param id 考试ID
	 * @param type 题目类型
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List seexid(int id,int type) throws SQLException {
		String se="select * from question where find_in_set(question_id,(select exam_question_ids from exam where exam_id=?)) and question.question_type=?";
		Connection conn= DbUtil.getconn();
		List list=runner.query(conn, se,new BeanListHandler<Question>(Question.class),id,type);
		conn.close();
		return list;
	}

	/**
	 * 通过考试 ID 题目类型得到对应的总分
	 * @param id 考试ID
	 * @param type 题目的类型
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int secount(int id,int type) throws SQLException {
		int zf=0;
		String sezf="select sum(question_score) from question where find_in_set(question_id,(select exam_question_ids from exam where exam_id="+id+")) and question.question_type="+type;
		Connection conn= DbUtil.getconn();
		Statement sta=conn.createStatement();
		ResultSet res=sta.executeQuery(sezf);
		if(res.next()){
			zf=res.getInt(1);
		}
		conn.close();
		return zf;
	}

	@Override
	public Object detaildsum(int id,int userid,String type) throws SQLException {
		String sezf="select sum(question_score) from detailpage where exam_id=? and user_id=? and exam_type=?";
		Connection conn= DbUtil.getconn();
		Object object=runner.query(conn, sezf,new ScalarHandler<Object>(),id,userid,type);
		conn.close();
		return object;
	}



	//模拟测试单选
	@Override
	public List<Question> getdan(int bid,int num) throws SQLException {
		Connection conn = DbUtil.getconn();
		String sql = "select * from question where question_bookid=? and question_type=1 order by rand() limit ?";
		List<Question> list = runner.query(conn,sql,new BeanListHandler<Question>(Question.class),bid,num);
		conn.close();
		return list;
	}
	//查询考试记录
	@Override
	public List<ExamRecord> getexamrecord(String sql,Object... objects) throws SQLException {
		Connection conn = DbUtil.getconn();
		List<ExamRecord> list = runner.query(conn,sql, new BeanListHandler<ExamRecord>(ExamRecord.class),objects);
		conn.close();
		return list;
	}

	//添加考试记录
	@Override
	public int addexamrecord(ExamRecord e) throws SQLException {
		String sql="insert into exam_record(user_id,exam_id,exam_score,exam_results,exam_time,exam_zt,exam_name,exam_type,exam_limit_time) values(?,?,?,?,?,?,?,?,?)";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[9];
		p[0]=e.getUser_id();
		p[1]=e.getExam_id();
		p[2]=e.getExam_score();
		p[3]=e.getExam_results();
		p[4]=e.getExam_time();
		p[5]=e.getExam_zt();
		p[6]=e.getExam_name();
		p[7]=e.getExam_type();
		p[8]=e.getExam_limit_time();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}

	//修改考试记录
	@Override
	public int updaterecord(ExamRecord e) throws SQLException {
		String sql="update exam_record set exam_score=?,exam_results=?,exam_time=?,exam_zt=? where user_id=? and exam_id=?";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[6];
		p[0]=e.getExam_score();
		p[1]=e.getExam_results();
		p[2]=e.getExam_time();
		p[3]=e.getExam_zt();
		p[4]=e.getUser_id();
		p[5]=e.getExam_id();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}

	/**
	 * 根据学生id查询其信息
	 * @param stuid
	 * @return Student
	 */
	@Override
	public Student getStudent(int stuid) {
		Connection conn = DbUtil.getconn();
		String sql = "select * from student where stid=?";
		Student stu = null;
		try {
			stu = runner.query(conn,sql,new BeanHandler<Student>(Student.class),stuid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stu;
	}

	/**
	 * 查询所有学生
	 * @return
	 */
	@Override
	public List getStudentList() {
		Connection conn = DbUtil.getconn();
		String sql = "select * from student";
		List list = null;
		try {
			list = runner.query(conn,sql,new BeanListHandler<Student>(Student.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 降序查询评论
	 * @return
	 */
	@Override
	public List querypl() {
		Connection conn = DbUtil.getconn();
		String sql = "select * from comment order by time desc;";
		List list = null;
		try {
			list = runner.query(conn,sql,new BeanListHandler<Comment>(Comment.class));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 添加评论
	 * @param com
	 * @return int
	 */
	@Override
	public int addComment(Comment com) {
		Connection conn = DbUtil.getconn();
		String sql = "insert into comment(uid,content,time) value(?,?,?);";
		Object[] obj = new Object[3];
		obj[0] = com.getUid();
		obj[1] = com.getContent();
		obj[2] = com.getTime();
		int i=0;
		try {
			i = runner.update(conn,sql,obj);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	/**
	 * 升序查询留言
	 * @return
	 */
	@Override
	public List queryly() {
		Connection conn = DbUtil.getconn();
		String sql = "select * from reply order by time asc;";
		List list = null;
		try {
			list = runner.query(conn,sql,new BeanListHandler<Reply>(Reply.class));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 添加留言
	 * @param reply
	 * @return int
	 */
	@Override
	public int addreply(Reply reply) {
		Connection conn = DbUtil.getconn();
		String sql = "insert into reply(uid,pid,type,cid,content,time) value(?,?,?,?,?,?);";
		Object[] obj = new Object[6];
		obj[0] = reply.getUid();
		obj[1] = reply.getPid();
		obj[2] = reply.getType();
		obj[3] = reply.getCid();
		obj[4] = reply.getContent();
		obj[5] = reply.getTime();
		int i=0;
		try {
			i = runner.update(conn,sql,obj);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	//月份查询
	@Override
	public List<HomeWork> addhom(String ctime) throws SQLException {
		Connection conn= DbUtil.getconn();
		String sql="select *from myhome where ctime like ?";
		String p="_____"+ctime+"___";
		List<HomeWork> list=runner.query(conn, sql,new BeanListHandler<HomeWork>(HomeWork.class),p);
		conn.close();
		return list;
	}




	//加入作业表
	@Override
	public int tjzy(HomeWork hom) throws SQLException {
		String sql="insert into homework(studentId,checkIn,work,ctime,remark) values(?,?,?,?,?)";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[5];
		p[0]=hom.getStudentId();
		p[1]=hom.getCheckIn();
		p[2]=hom.getWork();
		p[3]=hom.getCtime();
		p[4]=hom.getRemark();

		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}

	@Override
	public List<HomeWork> addid(String stname) throws SQLException {
		Connection conn= DbUtil.getconn();
		String sql="select stid from student where stname=?";
		List<HomeWork> list=runner.query(conn, sql,new BeanListHandler<HomeWork>(HomeWork.class),stname);
		conn.close();
		return list;
	}

	@Override
	public int delhom(int id) throws SQLException {
		Connection conn= DbUtil.getconn();
		String sql="DELETE FROM homework WHERE id=?";
		int n=runner.update(conn,sql,id);
		conn.close();
		return n;
	}

	/**
	 * 查询对应试卷的答案
	 * @param examid 考试的id
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List seanswer(int examid) throws SQLException {
		String sean=" select question_id,question_answer from question where find_in_set(question_id,(select exam_question_ids from exam where exam_id=?))";
		Connection conn= DbUtil.getconn();
		List lis=runner.query(conn,sean,new BeanListHandler<Question>(Question.class),examid);
		conn.close();
		return lis;
	}

	/**
	 * 通过考试查询考试的信息
	 * @param exanid 考试ID
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List seexamconfig(int exanid) throws SQLException {
		String secof=" select * from exam where exam_id=?";
		Connection conn= DbUtil.getconn();
		List lis=runner.query(conn,secof,new BeanListHandler<ExamRecord>(ExamRecord.class),exanid);
		conn.close();
		return lis;
	}
	//添加错题
	@Override
	public int addfalsebook(FalseBook fb) throws SQLException {
		String sql="insert into falsebook(question_id,user_id,exam_id,user_answer,question_answera,question_answerb,question_answerc,question_answerd,question_answer,exam_type,question_name) values(?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[11];
		p[0]=fb.getQuestion_id();
		p[1]=fb.getUser_id();
		p[2]=fb.getExam_id();
		p[3]=fb.getUser_answer();
		p[4]=fb.getQuestion_answera();
		p[5]=fb.getQuestion_answerb();
		p[6]=fb.getQuestion_answerc();
		p[7]=fb.getQuestion_answerd();
		p[8]=fb.getQuestion_answer();
		p[9]=fb.getExam_type();
		p[10]=fb.getQuestion_name();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}

	//添加试卷解析
	@Override
	public int adddetailpage(DetailPage fb) throws SQLException {
		String sql="insert into detailpage values(?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn=DbUtil.getconn();
		Object p[]=new Object[12];
		p[0]=fb.getQuestion_name();
		p[1]=fb.getQuestion_id();
		p[2]=fb.getUser_id();
		p[3]=fb.getExam_id();
		p[4]=fb.getUser_answer();
		p[5]=fb.getQuestion_answera();
		p[6]=fb.getQuestion_answerb();
		p[7]=fb.getQuestion_answerc();
		p[8]=fb.getQuestion_answerd();
		p[9]=fb.getQuestion_answer();
		p[10]=fb.getExam_type();
		p[11]=fb.getQuestion_score();
		int n=runner.update(conn,sql,p);
		conn.close();
		return n;
	}
	//查询错题本
	@Override
	public List falsebook(String sql,Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		List lis=runner.query(conn,sql,new BeanListHandler<FalseBook>(FalseBook.class),objects);
		conn.close();
		return lis;
	}

	//查询考试解析
	@Override
	public List detailpage(String sql,Object... objects) throws SQLException {
		Connection conn= DbUtil.getconn();
		List lis=runner.query(conn,sql,new BeanListHandler<DetailPage>(DetailPage.class),objects);
		conn.close();
		return lis;
	}

}
